import json
import re
import time
import os
import sqlite3

from qiboPOST import ContentConfig, bokeePOST
from tbitems import tbmain
from tbitems_db import initdb, insertdb,searchID
from maokoulingClass import maokouling_ONE, tblink_get_gaoyong, item_to_SELLID, userid_to_shopTBK_link
'''
------本文件为主文件--------
负责功能：  根据id 获取内容串，
调用函数：def tbpinglun(itemid)  获取评论内容
tbmain.py ----- 主文件
    tbitems_db.py    -----副文件,数据库操作
    tbitems.py       -----副文件，内容解析
    maokoulingClass.py ---副文件，瞄口令解析（需要配置账号信息）
--------------------------
'''


def opendb():
    dbpath = os.path.join('tb.db')
    conn = sqlite3.connect(dbpath)
    cursor = conn.cursor()

# 关闭数据库
def closedb(conn,cursor):
    conn.commit()
    cursor.close()
#存入数据库
def savemain(itemid,page):
    print(f'{itemid}和页码是{page}')
    # itemid = 588976817331
    # page = 2
    JSONlist=[]
    JSONlist=tbmain(itemid, page)  #爬虫request 获取到一个页面评论的json信息
    print(JSONlist)
    if JSONlist==[]:
        return None
    # 获取到list开始循环获得资源
    dbpath = os.path.join('tb.db')
    conn = sqlite3.connect(dbpath)
    cursor = conn.cursor()
    for i in JSONlist:
        if i['评论内容'] == '此用户没有填写评价。':
            continue
        try:
            initdb(cursor)  #如果没有则创建sql表
            insertdb(cursor,
                                itemid,
                                i['评论人'],
                                i['评论头像'],
                                i['评论时间'],
                                i['评论内容'],
                                i['购买商品类型'],
                                str(i['评论视频截图']),
                                str(i['评论视频']),
                                str(i['好评图片']))
        except Exception as e:
            print(e)
            print('数据库插入错误')
            pass
    closedb(conn, cursor)
    return 'ok'

#读取数据库内容
def readinfo(id):
    text = []
    dbpath = os.path.join('tb.db')
    conn = sqlite3.connect(dbpath)
    cursor = conn.cursor()
    try:
        JSONlist = searchID(cursor,id)
        # print(list(JSONlist[0]))
        # for i in JSONlist:
        #     print(i[4])
        #     text.append(i[4])
        # print(JSONlist)
        return JSONlist
    except Exception as e:
        print(e)
        #没有查到记录
        print("数据库没有该记录")
    # print(text)
    closedb(conn, cursor)

def tbpinglun(itemid):
    id = itemid
    message = id
    # 利用VIP目录的maokoulingClass.py 请求商品id获取对应id的URL
    accuntOne = maokouling_ONE()
    accuntOne.itemid = id
    item_TBK_url = tblink_get_gaoyong(accuntOne)
    accuntOne.userid = item_to_SELLID(accuntOne)
    shop_TBK_LINK = userid_to_shopTBK_link(accuntOne)

    # 爬取数据开始:
    result  = savemain(id, 1)
    if result == None:
        print('result == None')
        return None
    result = savemain(id, 2)
    result = savemain(id, 3)
    result = savemain(id, 4)

    # 爬取数据结束

    # #  读取并整合数据开始，获得某个id的所有db-json数据
    JSONlist = readinfo(id)
    # print(JSONlist)
    items = []  # 把db内容读成items列表
    pic = []  # 该id下面的数据库存储的图片列表
    picnum = 0
    num = 0
    jjword = ''  # 累计的一段话，每循环一次，后边组成+一句评价，计数20条后，重新累计成一段话
    word = []  # jjword累计够20条存入word段落中
    wordList = []  # 把每行评价都存到list列表里边
    # 遍历所有的评论数据
    for i in JSONlist:
        num = num + 1
        id = i[0]  # 获取一行db的id数据
        user_name = i[1]  # 获取一行db的用户数据
        user_face = i[2].replace("http;", "http:")
        user_date = i[3]
        user_text = i[4]
        info = i[5]
        videoimg = i[6].replace("http;", "http:")
        videoUrl = i[7].replace("http;", "http:")
        imglist = i[8].replace("http;", "http:")

        item = {
            'id': id,
            'user_name': user_name,
            'user_face': user_face,
            'user_date': user_date,
            'user_text': user_text,
            'info': info,
            'videoimg': videoimg,
            'videoUrl': videoUrl,
            'imglist': eval(imglist),  # 难点，imglist里边可能有1到n张图片，是个图片列表
        }
        items.append(item)  # items列表内是评价的信息列表
        if item['imglist'] != 'None':  # 如果这条评价不为空，就假如pic 列表

            for o in eval(imglist):
                if picnum < 10:
                    # print(o)
                    pic.append(o)
                else:
                    break
        picnum = picnum + 1
        jjword = jjword + item['user_text']
        # print(jjword)
        # print(num)
        # print(pic)

        wordList.append(item['user_text'])
        if num == 3 or num == 13 or num == 25 or num == 30 or num == 45 or num == 55 or num == 60:
            word.append(jjword)
            jjword = ''
    # print('11111111111111111111111111')
    # print(len(wordList))
    # print(pic)
    dumpspic = json.dumps(pic)
    abc = zip(word, pic)
    ################读取数据并整合结束
    '''
    需要的数据为：
    word 评论数据
    item_TBK_url  商品淘宝客连接
    shop_TBK_LINK 店铺淘宝客连接
    pic           图片列表

    '''
    try:
        postword =f'''<p style="text-indent:2em">{word[0]}</p><p style="text-indent:2em">{word[1]}</p><p><a class="fr-green" href="{item_TBK_url}" target="_blank"><strong><span style="color: rgb(255, 0, 0); font-size: 18px;">&gt;&gt;&gt;&gt;点击这里</span><span style="color: rgb(255, 0, 0);"><span style="font-size: 16px;">查看最新真实分析，以免被骗&lt;&lt;&lt;&lt;希望对你有用</span></span></strong></a><br><a href="{shop_TBK_LINK}" target="_blank"><strong><span style="font-size: 16px;">&gt;&gt;&gt;&gt;&gt;&gt;&gt;&gt;了解店铺更多详情(今日有活动领取优惠)</span></strong></a><span style="font-size: 18px;">&nbsp;</span></p><img class="center-block img-responsive" href="{pic[0]}" src="{pic[0]}"><img class="center-block img-responsive" href="{pic[1]}" src="{pic[1]}"><p style="text-indent:2em">{word[2]}</p><p style="text-indent:2em">{word[3]}</p><img class="center-block img-responsive" href="{pic[2]}" src="{pic[2]}"><p style="text-indent:2em">{word[4]}</p><p style="text-indent:2em">{word[5]}</p><img class="center-block img-responsive" href="{pic[3]}" src="{pic[3]}"><img class="center-block img-responsive" href="{pic[4]}" src="{pic[4]}"><p style="text-indent:2em">{word[6]}</p><p style="text-align: center;"><strong><span style="font-size: 18px; color: rgb(147, 101, 184);">&gt;&gt;</span><span style="font-size: 18px; color: rgb(255, 0, 0);">隐藏福利 送你一个随时可以查优惠券的机器人，加微信免费领：buy3600</span></strong></p>'''
        print(postword)
    except:
        # exit(code='产品现在没有足够的评论，采集发帖失败')
        return None
    return postword


def Run(ipostnum):
    postnum = 1
    '''1、取id'''
    dbpath = os.path.join('tb.db')
    print(dbpath)
    conn = sqlite3.connect(dbpath)
    cursor = conn.cursor()
    rowid = cursor.execute('SELECT rowid From JiSuBBS')
    lenofDB = len(rowid.fetchall())
    for num in range(1,lenofDB):
        print(num)
        result = cursor.execute(f"SELECT itemid,链接,备用,title FROM JiSuBBS where rowid ={num}").fetchone()
        conn.commit()
        print(result)
        if result[2] =='3':
            print('111111111111111111111111111111111111111111111111111111111111111111111111111111')
            continue
        else:
            itemid = result[0]
            print(itemid)
            #id是空的就过滤掉
            if itemid == 0:
                print('数据库里边没有id号,把这条过滤掉')
                continue
            # '''2、赋值内容'''
            content =tbpinglun(itemid)
            if content == None:   #判断内容是否足够四页
                cursor.execute(f'update JiSuBBS set 备用 = 3,生成评论内容 = "评论数量不够" where 链接= "{result[1]}"')
                # cursor.execute(f'update JiSuBBS set 备用 = 3,生成评论内容 = {content}')
                conn.commit()
                continue
            #过滤所有的非法字符
            def filter_emoji(desstr, restr=''):
                # 过滤表情
                try:
                    co = re.compile(u'[\U00010000-\U0010ffff]')
                except re.error:
                    co = re.compile(u'[\uD800-\uDBFF][\uDC00-\uDFFF]')
                return co.sub(restr, desstr)

            content = str(filter_emoji(content))

            #
            #
            # '''3、发博客'''
            # # 调用企业博客发帖模块函数块需要的内容----START
            bokeeconfig = ContentConfig()
            bokeeconfig.title = result[3],
            bokeeconfig.content = content,
            bokeeconfig.columnName = '值得买',  # 分类名字1
            bokeeconfig.addType = '值得买',  # 分类2
            bokeeconfig.tags = '',  # 标签
            bokeeconfig.brief = result[3]  # 内容摘要
            bokeePOST(bokeeconfig)
            # # 调用企业博客发帖模块函数块需要的内容----END
            cursor.execute(f"update JiSuBBS set 备用 = 3,生成评论内容 = '{content}' where 链接= '{result[1]}'")
            # cursor.execute(f'update JiSuBBS set 备用 = 3,生成评论内容 = {content}')
            conn.commit()
            time.sleep(5)
            if postnum >= ipostnum:
                exit(code='发帖完毕')
            postnum =postnum+1
    cursor.close()

if __name__ == '__main__':
    Run(1)